package com.util;

import java.sql.SQLException;
import java.util.Calendar;

import com.db.DbUtil;

public class TestPartition {
	private static int col=300;

	void createTable(int part) throws SQLException{
		DbUtil.update("drop table if exists test");
		StringBuilder sql=new StringBuilder();
		sql.append("create table test(");
		sql.append("id int not null auto_increment,cycle int(6),");
		for(int i=1;i<=col;i++){
			sql.append("name").append(i).append(" varchar(20),");
		}
		sql.append("key (cycle),key (id),");
		sql.deleteCharAt(sql.length()-1).append(")");
		sql.append("partition by list(cycle)(");
		Calendar c=Calendar.getInstance();
		for(int i=12;i>=1;i--){
			sql.append("partition part").append(i).append(" values in (").append((c.get(Calendar.YEAR)-part+1)+""+(i>9?i:"0"+i)).append("),");
		}
		sql.deleteCharAt(sql.length()-1).append(");");
		System.out.println(sql);
		DbUtil.update(sql.toString());
	}
	void testSelect(int count,int part) throws SQLException{
		int id=(int) Math.round(Double.parseDouble(""+Math.random()*count));
		long time=0;
		for(int i=1;i<=count;i++){
			long s=System.currentTimeMillis();
			DbUtil.queryForMap("select * from test where id="+id, String.class);
			long e=System.currentTimeMillis();
			time+=e-s;
		}
		System.out.println("select avag="+1.0*time/count);
	}
	void testInsert(int count,int part) throws SQLException{
		long s=System.currentTimeMillis();
		StringBuilder sb=new StringBuilder("insert into test values");
		int avg=count/part;
		for(int p=1;p<=part;p++){
			for(int c=1;c<=avg;c++){
				Calendar calendar=Calendar.getInstance();
				String month=c%12+1>9?""+(c%12+1):"0"+(c%12+1);
				String cycle=(calendar.get(Calendar.YEAR)-p+1)+""+month;
				sb.append("(null,").append(cycle).append(",");
				for(int i=1;i<=col;i++){
					sb.append("name"+i).append(",");
				}
				sb.deleteCharAt(sb.length()-1).append("),");
				if(c%300==0){
					DbUtil.insert(sb.deleteCharAt(sb.length()-1).toString());
					sb=new StringBuilder("insert into test values");
				}
			}
		}
		long e=System.currentTimeMillis();
		System.out.println("insert cost:"+(e-s));
	}
	/*void testInsert(int count,int part,boolean random)throws SQLException{
		long s=0;
		for(int i=1;i<=count;i++){
			StringBuilder sb=new StringBuilder();
			long r=Math.round(Double.parseDouble(""+Math.random()*100));
			long p=Math.round(Double.parseDouble(""+Math.random()));
			String cycle=2012-r%(part*12)
			sb.append("(null,").append(cycle).append(",");
			for(int i=1;i<=col;i++){
				sb.append("name"+i).append(",");
			}
			sb.deleteCharAt(sb.length()-1).append("),");
		}
	}*/
	public static void main(String[] args) throws SQLException {
		TestPartition t=new TestPartition();
		t.createTable(1);
		t.testSelect(10, 1);
		t.testInsert(1000, 1);
		//System.out.println(Integer.MAX_VALUE);
	}

}

